package com.smart.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.smart.model.Delivery;
import com.smart.model.RepairRequest;
import com.smart.util.DBUtil;

public class DeliveryDAO {
	
	public List<Delivery> getDeliveryList(String phone, int status, int page, int pageSize) throws SQLException {
		List<Delivery> list = new ArrayList<Delivery>();
		Connection conn = DBUtil.getConnection();
		PreparedStatement pSt;
		
		String sql = "select * from delivery where telphone_num = ? and status = ? order by date desc limit ?, ?";
		pSt = conn.prepareCall(sql);
		pSt.setString(1, phone);
		pSt.setInt(2, status);
		pSt.setInt(3, (page - 1) * pageSize);
		pSt.setInt(4, pageSize);
		
		ResultSet rs = pSt.executeQuery();
		while(rs.next()) {
			Delivery delivery = new Delivery();
			delivery.setId(rs.getInt("id"));
			delivery.setCommunityId(rs.getInt("community_id"));
			delivery.setTelephoneNum(rs.getString("telphone_num"));
			delivery.setUserName(rs.getString("user_name"));
			delivery.setExtraInfo(rs.getString("extra_info"));
			delivery.setDeliveryCom(rs.getString("delivery_com"));
			delivery.setOrderNum(rs.getString("order_num"));
			DateFormat format = new SimpleDateFormat("yyyy-MM-dd"); 
			delivery.setDate(format.format(rs.getDate("date")));
			delivery.setStatus(rs.getInt("status"));
			list.add(delivery);
		}
		return list;
	}

	public void addDelivery(Delivery delivery) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql="insert into delivery(community_id, telphone_num, user_name, extra_info, delivery_com, order_num, date, status) values(?,?,?,?,?,?,?,?);";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setInt(1, delivery.getcommunityID());
		pSt.setString(2, delivery.getTelephoneNum());
		pSt.setString(3, delivery.getUserName());
		pSt.setString(4, delivery.getExtraInfo());
		pSt.setString(5, delivery.getDeliveryCom());
		pSt.setString(6, delivery.getOrderNum());
		Date date = Date.valueOf(delivery.getDate());
		pSt.setDate(7, date);
		pSt.setInt(8, delivery.getStatus());
		pSt.execute();
		pSt.close();		
	}
	
	public List<Delivery> searchDelivery(String telephoneNum, String timeFrom, String timeTo, int status) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select * from delivery where 1=1 ";
		//PreparedStatement pSt = conn.prepareCall(sql);
		if(!telephoneNum.isEmpty()) {
			sql += " and telphone_num='"+telephoneNum+"'";
		}
		if(!timeFrom.isEmpty()){
			sql += " and date>='"+timeFrom+"'";
		}
		if(!timeTo.isEmpty()){
			sql += " and date<='"+timeTo+"'";
		}
		if(!timeTo.isEmpty()){
			sql += " and date<='"+timeTo+"'";
		}
		if(status != -1){
			sql += " and status="+status;
		}
		Statement st = conn.createStatement();
		System.out.println("sql:"+sql);
		ResultSet res = st.executeQuery(sql);
		List<Delivery> deliveryList = new ArrayList<Delivery>();
		while(res.next()){
			Delivery delivery = new Delivery();
			delivery.setId(res.getInt("id"));
			delivery.setCommunityId(res.getInt("community_id"));
			delivery.setDeliveryCom(res.getString("delivery_com"));
			delivery.setTelephoneNum(res.getString("telphone_num"));
			delivery.setUserName(res.getString("user_name"));
			delivery.setOrderNum(res.getString("order_num"));
			delivery.setExtraInfo(res.getString("extra_info"));
			delivery.setDate(res.getDate("date").toString());
			delivery.setStatus(res.getInt("status"));
			System.out.println(delivery.getUserName());
			deliveryList.add(delivery);
		}
		return deliveryList;
	}
	
	public Delivery getDeliveryById(int id) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select * from delivery where id=?;";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setInt(1, id);
		ResultSet res = pSt.executeQuery();
		Delivery delivery = new Delivery();
		if(res.next()) {
			delivery.setId(res.getInt("id"));
			delivery.setCommunityId(res.getInt("community_id"));
			delivery.setDeliveryCom(res.getString("delivery_com"));
			delivery.setTelephoneNum(res.getString("telphone_num"));
			delivery.setUserName(res.getString("user_name"));
			delivery.setOrderNum(res.getString("order_num"));
			delivery.setExtraInfo(res.getString("extra_info"));
			delivery.setDate(res.getDate("date").toString());
			delivery.setStatus(res.getInt("status"));
		}
		return delivery;
		
	}
	
	public boolean updateDeliveryStatById(int id, int status) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "update delivery set status=? where id=?;";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setInt(1, status);
		pSt.setInt(2, id);
		if(pSt.execute())
			return true;
		else 
			return false;
	}
	
	public List<Delivery> getDeliveryInPage(int page, int pageSize) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select * from delivery order by id desc limit ?,?;";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setInt(1, (page-1)*pageSize);
		pSt.setInt(2, page*pageSize);
		ResultSet res = pSt.executeQuery();
		List<Delivery> list = new ArrayList<Delivery>();
		while(res.next()) {
			Delivery delivery = new Delivery();
			delivery.setId(res.getInt("id"));
			delivery.setCommunityId(res.getInt("community_id"));
			delivery.setDeliveryCom(res.getString("delivery_com"));
			delivery.setTelephoneNum(res.getString("telphone_num"));
			delivery.setUserName(res.getString("user_name"));
			delivery.setOrderNum(res.getString("order_num"));
			delivery.setExtraInfo(res.getString("extra_info"));
			delivery.setDate(res.getDate("date").toString());
			delivery.setStatus(res.getInt("status"));
			list.add(delivery);
		}
		return list;
	}
	
	public int getCount() throws SQLException {
		Connection conn = DBUtil.getConnection();
		int count=-1;
		String sql="select count(*) as con from delivery";
	
		PreparedStatement pSt=conn.prepareStatement(sql);
		ResultSet res=pSt.executeQuery();
		if(res.next())
			count=res.getInt("con");
		return count;
	}
}
